<?php

namespace App\Models;

class Student extends BaseModel
{
    protected static string $table = 'students';

    public static function search(array $filters, int $page = 1, int $pageSize = 20): array
    {
        $db = self::getDb();
        $offset = ($page - 1) * $pageSize;
        
        $sql = "SELECT * FROM `students` WHERE 1=1";
        $params = [];
        
        if (!empty($filters['keyword'])) {
            $sql .= " AND (`student_id` LIKE ? OR `name` LIKE ?)";
            $keyword = "%{$filters['keyword']}%";
            $params[] = $keyword;
            $params[] = $keyword;
        }
        
        if (!empty($filters['major'])) {
            $sql .= " AND `major` = ?";
            $params[] = $filters['major'];
        }
        
        if (!empty($filters['className'])) {
            $sql .= " AND `class_name` = ?";
            $params[] = $filters['className'];
        }
        
        if (!empty($filters['grade'])) {
            $sql .= " AND `grade` = ?";
            $params[] = $filters['grade'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND `status` = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['gender'])) {
            $sql .= " AND `gender` = ?";
            $params[] = $filters['gender'];
        }
        
        // 获取总数
        $countSql = preg_replace('/SELECT \*/', 'SELECT COUNT(*) as count', $sql);
        $countResult = $db->queryOne($countSql, $params);
        $total = (int) $countResult['count'];
        
        // 获取数据
        $sql .= " ORDER BY `created_at` DESC LIMIT {$pageSize} OFFSET {$offset}";
        $list = $db->query($sql, $params);
        
        return [
            'list' => $list,
            'total' => $total
        ];
    }

    public static function getMajors(): array
    {
        $db = self::getDb();
        $result = $db->query("SELECT DISTINCT `major` FROM `students` ORDER BY `major`");
        return array_column($result, 'major');
    }

    public static function getClasses(?string $major = null): array
    {
        $db = self::getDb();
        
        $sql = "SELECT DISTINCT `class_name` FROM `students`";
        $params = [];
        
        if ($major) {
            $sql .= " WHERE `major` = ?";
            $params[] = $major;
        }
        
        $sql .= " ORDER BY `class_name`";
        
        $result = $db->query($sql, $params);
        return array_column($result, 'class_name');
    }

    public static function findByStudentId(string $studentId): ?array
    {
        $db = self::getDb();
        return $db->queryOne(
            "SELECT * FROM `students` WHERE `student_id` = ?",
            [$studentId]
        );
    }

    public static function batchInsert(array $students): int
    {
        $db = self::getDb();
        $count = 0;
        
        try {
            $db->beginTransaction();
            
            foreach ($students as $student) {
                $db->insert('students', $student);
                $count++;
            }
            
            $db->commit();
        } catch (\Exception $e) {
            $db->rollback();
            throw $e;
        }
        
        return $count;
    }
}

